/*  
 * Copyright (c) 2008 Daniel Rinehart
 * http://danielr.neophi.com/
 * 
 * Permission is hereby granted, free of charge, to any person obtaining
 * a copy of this software and associated documentation files (the
 * "Software"), to deal in the Software without restriction, including
 * without limitation the rights to use, copy, modify, merge, publish,
 * distribute, sublicense, and/or sell copies of the Software, and to
 * permit persons to whom the Software is furnished to do so, subject to
 * the following conditions:
 * 
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
 * LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
 * OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
 * WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
package com.ninearrows.db
{
    /**
     * Define the SQL statements that are used in the application 
     * and referenced by multiple classes.
     */
    public class Statements
    {
        public static const INIT_USER_QUERY:String =
            "DELETE FROM mst_users";

/**** 参照　****/

        public static const GET_USER_QUERY:String =
            "SELECT id, login_id, password FROM mst_users WHERE login_id = :login_id;";

        public static const UPDATE_PASSWORD_QUERY:String =
            "UPDATE mst_users SET  password = :new_password WHERE login_id = :login_id;";

        public static const MESSAGE_LIST_QUERY:String =
            "SELECT id, msg_code, msg_kbn, msg_base, msg_jp, msg_cn, msg_en FROM mst_messages WHERE valid_flg='1' ;";

        public static const PROJECT_LIST_QUERY:String =
            "SELECT id, project_cd, project_name, start_date, delivery_date, valid_flg, create_user_id, created_on, update_user_id, updated_on, end_user_name FROM dat_projects ;";


        public static const WBS_LIST_QUERY:String =
            "SELECT " +
            "	dat_projectcomps.id, " +
            "	dat_projectcomps.project_id, " +
            "	dat_projectcomps.line_no, " +
            "	dat_projectcomps.task_kbn, " +
            "	dat_projectcomps.item_name AS item_name, " +
            "	dat_projectcomps.class_word1, " +
            "	dat_projectcomps.class_word2, " +
            "	dat_projectcomps.class_word3, " +
            "	dat_projectcomps.create_user_id, " +
            "	dat_projectcomps.created_on, " +
            "	dat_projectcomps.update_user_id, " +
            "	dat_projectcomps.updated_on, " +
            "	dat_projectcomps.last_operation_kbn, " +
            "	COALESCE(mst_users.user_name,mst_users.email) AS user_name, " +
            "	mst_users.id AS user_id, " +
            "	dat_tasks.id AS dat_tasks_id, " +
            "	dat_tasks.progress_kbn AS progress_kbn, " +
            "	dat_tasks.priority_kbn AS priority_kbn, " +
            "	dat_tasks.task_cd AS task_cd, " +
            "	dat_tasks.plan_power AS plan_power, " +
            "	dat_tasks.fcas_power AS fcas_power, " +
            "	dat_tasks.client_user_id AS client_user_id, " +
            "	dat_tasks.main_user_id AS main_user_id, " +
            "	dat_tasks.complete_date AS complete_date, " +
            "	dat_tasks.progress_kbn AS progress_kbn, " +
            "	dat_tasks.progress_rate AS progress_rate, " +
            "	dat_tasks.memo AS dat_tasks_memo, " +
            
            "   dat_milestones.id AS dat_mile_id, " +
            "   dat_events.id AS dat_event_id, " +
            
            "	CASE " +
            "	WHEN dat_projectcomps.task_kbn='1' THEN dat_tasks.complete_date " +
            "	WHEN dat_projectcomps.task_kbn='2' THEN dat_milestones.mils_date " +
            "	WHEN dat_projectcomps.task_kbn='3' THEN dat_events.end_date " +
            "	END AS comp_exp_date, " +
            "	CASE " +
            "	WHEN dat_projectcomps.task_kbn='1' THEN COALESCE(mst_users_dat_projectusers.user_name,mst_users_dat_projectusers.email) " +
            "	ELSE COALESCE(create_user.user_name,create_user.email) " +
            "	END AS client_user_name " +
            " FROM " +
            "	dat_projects " +
            " LEFT JOIN " +
            "	dat_projectcomps " +
            "	ON " +
            "	dat_projectcomps.project_id= dat_projects.id" +
            " LEFT JOIN " +
            "	dat_tasks " +
            "	ON " +
            "	dat_tasks.project_tree_id= dat_projectcomps.id" +
            " LEFT JOIN " +
            "	dat_projectusers " +
            "	ON " +
            "	dat_projectusers.id= dat_tasks.main_user_id" +
            " LEFT JOIN " +
            "	mst_users" +
            "	ON " +
            "	dat_projectusers.user_id= mst_users.id" +
            " LEFT JOIN " +
            "	dat_projectusers AS dat_user_clients_dat_tasks " +
            "	ON " +
            "	dat_user_clients_dat_tasks.id= dat_tasks.client_user_id" +
            " LEFT JOIN " +
            "	mst_users AS mst_users_dat_projectusers" +
            "	ON " +
            "	mst_users_dat_projectusers.id= dat_user_clients_dat_tasks.user_id" +
            " LEFT JOIN " +
            "	mst_users AS task_client_user" +
            "	ON " +
            "	dat_tasks.client_user_id= task_client_user.id" +
            " LEFT JOIN " +
            "	mst_users AS create_user" +
            "	ON " +
            "	dat_projectcomps.create_user_id= create_user.id" +
            " LEFT JOIN " +
            "	dat_milestones " +
            "	ON " +
            "	dat_milestones.project_tree_id= dat_projectcomps.id" +
            " LEFT JOIN " +
            "	dat_events " +
            "	ON " +
            "	dat_events.project_tree_id= dat_projectcomps.id" +
            " WHERE " +
            "	dat_projects.id=:project_id ;"

        public static const MYTASK_LIST_QUERY:String =
            "SELECT " +
            "   dat_projectcomps.id, " +
            "   dat_projectcomps.project_id, " +
            "   dat_projectcomps.line_no, " +
            "   dat_projectcomps.task_kbn, " +
            "   dat_projectcomps.item_name AS item_name, " +
            "   dat_projectcomps.class_word1, " +
            "   dat_projectcomps.class_word2, " +
            "   dat_projectcomps.class_word3, " +
            "   dat_projectcomps.create_user_id, " +
            "   dat_projectcomps.created_on, " +
            "   dat_projectcomps.update_user_id, " +
            "   dat_projectcomps.updated_on, " +
            "   dat_projectcomps.last_operation_kbn, " +
            "   COALESCE(mst_users.user_name,mst_users.email,dat_projectusers.email) AS user_name, " +
            "   dat_projectusers.id as projectuser_id, " +
            "   mst_users.id AS user_id, " +
            "   dat_tasks.id AS dat_tasks_id, " +
            "   dat_tasks.progress_kbn AS progress_kbn, " +
            "   dat_tasks.priority_kbn AS priority_kbn, " +
            "   dat_tasks.task_cd AS task_cd, " +
            "   dat_tasks.plan_power AS plan_power, " +
            "   dat_tasks.fcas_power AS fcas_power, " +
            "   dat_tasks.client_user_id AS client_user_id, " +
            "   dat_tasks.main_user_id AS main_user_id, " +
            "   dat_tasks.complete_date AS complete_date, " +
            "   dat_tasks.progress_kbn AS progress_kbn, " +
            "   dat_tasks.progress_rate AS progress_rate, " +
            "   dat_tasks.memo AS dat_tasks_memo, " +
            "   dat_tasks.result AS dat_tasks_result, " +
            "   COALESCE(task_client_user.user_name,dat_user_clients_dat_tasks.email) AS task_client_user_name, " +
            "   COALESCE(task_main_user.user_name, task_main_user.email) AS task_main_user_name, " +
            
            "   CASE " +
            "   WHEN dat_projectcomps.task_kbn='1' THEN dat_tasks.complete_date " +
            //"   WHEN dat_projectcomps.task_kbn='2' THEN dat_milestones.mils_date " +
            //"   WHEN dat_projectcomps.task_kbn='3' THEN dat_events.end_date " +
            "   END AS comp_exp_date, " +
            "   CASE " +
            "   WHEN dat_projectcomps.task_kbn='1' THEN COALESCE(mst_users_dat_projectusers.user_name,mst_users_dat_projectusers.email) " +
            "   ELSE COALESCE(create_user.user_name,create_user.email) " +
            "   END AS client_user_name " +
            " FROM " +
            "   dat_projects " +
            "   LEFT JOIN dat_projectcomps ON dat_projectcomps.project_id = dat_projects.id" +
            "   LEFT JOIN dat_tasks        ON dat_tasks.project_tree_id   = dat_projectcomps.id" +
            "   LEFT JOIN dat_taskusers    ON dat_taskusers.task_id       = dat_tasks.id" +
            "   LEFT JOIN dat_projectusers ON dat_projectusers.id         = dat_taskusers.projectuser_id" +
            "   LEFT JOIN mst_users        ON dat_projectusers.user_id    = mst_users.id" +
            "   LEFT JOIN dat_projectusers AS dat_user_main_dat_tasks    ON dat_user_main_dat_tasks.id    = dat_tasks.main_user_id" +
            "   LEFT JOIN dat_projectusers AS dat_user_clients_dat_tasks ON dat_user_clients_dat_tasks.id = dat_tasks.client_user_id" +
            "   LEFT JOIN mst_users AS mst_users_dat_projectusers ON mst_users_dat_projectusers.id   = dat_user_clients_dat_tasks.user_id" +
            "   LEFT JOIN mst_users AS task_client_user           ON dat_tasks.client_user_id        = task_client_user.id" +
            "   LEFT JOIN mst_users AS task_main_user             ON dat_user_main_dat_tasks.user_id          = task_main_user.id" +
            "   LEFT JOIN mst_users AS create_user                ON dat_projectcomps.create_user_id = create_user.id" +
/*
            " LEFT JOIN " +
            "   dat_milestones " +
            "   ON " +
            "   dat_milestones.project_tree_id= dat_projectcomps.id" +
            " LEFT JOIN " +
            "   dat_events " +
            "   ON " +
            "   dat_events.project_tree_id= dat_projectcomps.id" +
*/
            " WHERE " +
            "   dat_projects.id = :project_id " +
            "   AND dat_projectusers.user_id = :main_user_id " +
            " ORDER BY dat_tasks.progress_kbn ASC, comp_exp_date ASC"

        public static const PROJECT_QUERY:String =
            "SELECT id, project_cd, project_name, start_date, delivery_date, valid_flg, create_user_id, created_on, update_user_id, updated_on, end_user_name FROM dat_projects WHERE id = :id ;";

        public static const PROJECTCOMP_QUERY:String =
            "SELECT * FROM dat_projectcomps WHERE id = :id";

        public static const PROJECTUSER_QUERY:String =
            "SELECT dat_projectusers.local_id, dat_projectusers.id, dat_projectusers.project_id, " + 
            "dat_projectusers.user_id, dat_projectusers.create_user_id, " + 
            "mst_users.user_name AS user_name, mst_users.email AS email, " +
            "dat_projectusers.active_flg AS active_flg " +
            "FROM dat_projectusers " + 
            "JOIN mst_users " + 
            "ON mst_users.id=dat_projectusers.user_id " + 
            "WHERE project_id = :project_id";
            "ORDER BY dat_projectusers.id ;";

        public static const TASK_QUERY:String =
            "SELECT " + 
            "dat_tasks.local_id AS local_id, " + 
            "dat_tasks.id AS id, " + 
            "dat_tasks.project_tree_id AS project_tree_id, " + 
            "dat_tasks.priority_kbn AS priority_kbn, " + 
            "dat_tasks.plan_power AS plan_power, " + 
            "dat_tasks.fcas_power AS fcas_power, " + 
            "dat_tasks.exp_power AS exp_power, " + 
            "dat_tasks.tani_kbn AS tani_kbn, " + 
            "dat_tasks.client_user_id AS client_user_id, " + 
            "client_user.user_name AS client_user_name, " + 
            "client_user.email AS client_user_email, " +
            "dat_tasks.main_user_id AS main_user_id, " + 
            "main_user.user_name AS main_user_name, " + 
            "main_user.email AS main_user_email, " +
            "dat_tasks.complete_date AS complete_date, " + 
            "dat_tasks.progress_kbn AS progress_kbn, " + 
            "dat_tasks.progress_rate AS progress_rate, " + 
            "dat_tasks.memo AS memo, " + 
            "dat_tasks.create_user_id AS create_user_id, " + 
            "create_user.user_name AS create_user_name, " + 
            "create_user.email AS create_user_email, " +
            "dat_tasks.created_on AS created_on, " + 
            "dat_tasks.update_user_id AS update_user_id, " + 
            "update_user.user_name AS update_user_name, " + 
            "update_user.email AS update_user_email, " +
            "dat_tasks.updated_on AS updated_on, " + 
            "dat_tasks.task_cd AS task_cd, " + 
            "dat_tasks.start_date AS start_date, " + 
            "dat_tasks.end_date AS end_date, " + 
            "dat_tasks.report_date AS report_date, " + 
            "dat_tasks.result AS result, " + 
            "dat_tasks.last_operation_kbn AS last_operation_kbn, " + 
            "dat_projectcomps.item_name AS item_name, " + 
            "dat_projectcomps.task_kbn AS task_kbn, " + 
            "dat_projectcomps.class_word1 AS class_word1, " + 
            "dat_projectcomps.class_word2 AS class_word2, " + 
            "dat_projectcomps.class_word3 AS class_word3 " + 
            "FROM dat_tasks " + 
            "JOIN dat_projectcomps ON dat_projectcomps.id = dat_tasks.project_tree_id " + 
            "JOIN dat_projectusers AS project_client_user ON project_client_user.id = dat_tasks.client_user_id " + 
            "LEFT JOIN mst_users AS client_user ON client_user.id = project_client_user.user_id " + 
            "JOIN dat_projectusers AS project_main_user ON project_main_user.id = dat_tasks.main_user_id " + 
            "JOIN mst_users AS main_user ON main_user.id = project_main_user.user_id " + 
            "LEFT JOIN mst_users AS create_user ON create_user.id=dat_tasks.create_user_id " + 
            "LEFT JOIN mst_users AS update_user ON update_user.id=dat_tasks.update_user_id " + 
            "WHERE dat_tasks.id = :id ;";

        public static const TASKUSER_QUERY:String =
            "SELECT dat_taskusers.local_id, dat_taskusers.id, dat_taskusers.task_id, " + 
            "dat_taskusers.projectuser_id, dat_taskusers.create_user_id, " + 
            "mst_users.user_name AS user_name, mst_users.email AS email " +
            "FROM dat_taskusers " + 
            "JOIN dat_projectusers ON dat_projectusers.id = dat_taskusers.projectuser_id " + 
            "JOIN mst_users ON mst_users.id = dat_projectusers.user_id " + 
            "WHERE dat_taskusers.task_id = :task_id " + 
            "ORDER BY dat_taskusers.projectuser_id ;";




/**** 更新　****/
        public static const DELETE_TASK_DATA_QUERY:String =
            "DELETE " + 
            "FROM " + 
            " dat_projectcomps " + 
            "WHERE id = :id ;";



        public static const UPDATE_PROJECTCOMP_DATA_QUERY:String =
            "UPDATE " + 
            "	dat_projectcomps " + 
            "SET " + 
            "	item_name=:item_name, " + 
            "	class_word1=:class_word1, " + 
            "	class_word2=:class_word2, " +
            "	class_word3=:class_word3, " +
            "	update_user_id=:update_user_id, " +
            "	updated_on=:updated_on, " +
            "	local_edit_flg=1 " +
            "WHERE id = :id;";

        public static const UPDATE_SYNC_PROJECTCOMP_DATA_QUERY:String =
            "UPDATE " + 
            "   dat_projectcomps " + 
            "SET " + 
            "   item_name=:item_name, " + 
            "   class_word1=:class_word1, " + 
            "   class_word2=:class_word2, " +
            "   class_word3=:class_word3, " +
            "   update_user_id=:update_user_id, " +
            "   updated_on=:updated_on, " +
            "   local_edit_flg=0 " +
            "WHERE id = :id;";


        public static const UPDATE_TASK_DATA_QUERY:String =
            "UPDATE " + 
            "	dat_tasks " + 
            "SET " + 
            "	priority_kbn=:priority_kbn, " + 
            "	plan_power=:plan_power, " + 
            "	tani_kbn=:tani_kbn, " + 
            "	complete_date=:complete_date, " + 
            "	start_date=:start_date, " + 
            "	end_date=:end_date, " + 
            "	client_user_id=:client_user_id, " + 
            "   main_user_id=:main_user_id, " + 
            "	result=:result, " + 
            "	memo=:memo, " + 
            "	update_user_id=:update_user_id, " +
            "	updated_on=:updated_on, " +
            "   progress_kbn=:progress_kbn, " +
            "	local_edit_flg=1 " +
            "WHERE id = :id;";

        public static const UPDATE_SYNC_TASK_DATA_QUERY:String =
            "UPDATE " + 
            "   dat_tasks " + 
            "SET " + 
            "   priority_kbn=:priority_kbn, " + 
            "   plan_power=:plan_power, " + 
            "   tani_kbn=:tani_kbn, " + 
            "   complete_date=:complete_date, " + 
            "   start_date=:start_date, " + 
            "   end_date=:end_date, " + 
            "   client_user_id=:client_user_id, " + 
            "   main_user_id=:main_user_id, " + 
            "   result=:result, " + 
            "   memo=:memo, " + 
            "   update_user_id=:update_user_id, " +
            "   updated_on=:updated_on, " +
            "   last_operation_kbn=:last_operation_kbn, " +
            "   progress_kbn=:progress_kbn, " +
            "   progress_rate=:progress_rate, " +
            "   local_edit_flg=0 " +
            "WHERE id = :id;";


        public static const DELETE_TASKUSERS_DATA_QUERY:String =
            "DELETE " + 
            "FROM " + 
            "	dat_taskusers " + 
            "WHERE task_id = :task_id;";


        public static const INSERT_TASKUSERS_DATA_QUERY:String =
            "INSERT INTO " + 
            " dat_taskusers " + 
            " ( " + 
            "	task_id, " + 
            "	projectuser_id, " + 
            "	create_user_id, " + 
            "	created_on, " + 
            "	local_edit_flg " + 
            " ) " + 
            " VALUES " + 
            " ( " + 
            "	:task_id, " + 
            "	:projectuser_id, " + 
            "	:create_user_id, " + 
            "	:created_on, " + 
            "	1 " + 
            " );";

        public static const MILESTONE_LOAD_QUERY:String =
            "SELECT " + 
            "dat_milestones.local_id AS local_id, " + 
            "dat_milestones.id AS id, " + 
            "dat_milestones.project_tree_id AS project_tree_id, " + 
            "dat_milestones.mils_date AS mils_date, " + 
            "dat_milestones.last_operation_kbn AS last_operation_kbn, " + 
            "create_user.user_name AS create_user_name, " + 
            "create_user.email AS create_user_email, " +
            "dat_milestones.created_on AS created_on, " + 
            "dat_milestones.update_user_id AS update_user_id, " + 
            "update_user.user_name AS update_user_name, " + 
            "update_user.email AS update_user_email, " +
            "dat_milestones.updated_on AS updated_on, " + 
            "dat_projectcomps.item_name AS item_name, " + 
            "dat_projectcomps.task_kbn AS task_kbn, " + 
            "dat_projectcomps.class_word1 AS class_word1, " + 
            "dat_projectcomps.class_word3 AS class_word2, " + 
            "dat_projectcomps.class_word3 AS class_word3 " + 
            "FROM dat_milestones JOIN dat_projectcomps ON dat_projectcomps.id=dat_milestones.project_tree_id " + 
            "LEFT JOIN mst_users AS create_user ON create_user.id=dat_milestones.create_user_id " + 
            "LEFT JOIN mst_users AS update_user ON update_user.id=dat_milestones.update_user_id " + 
            "WHERE dat_milestones.id = :id ;";

        public static const EVENT_LOAD_QUERY:String =
            "SELECT " + 
            "dat_events.local_id AS local_id, " + 
            "dat_events.id AS id, " + 
            "dat_events.project_tree_id AS project_tree_id, " + 
            "dat_events.last_operation_kbn AS last_operation_kbn, " + 
            "dat_events.start_date AS start_date, " + 
            "dat_events.start_time AS start_time, " + 
            "dat_events.end_date AS end_date, " + 
            "dat_events.end_time AS end_time, " + 
            "dat_events.allday_kbn AS allday_kbn, " + 
            "dat_events.place AS place, " + 
            "dat_events.content AS content, " + 
            "create_user.user_name AS create_user_name, " + 
            "create_user.email AS create_user_email, " +
            "dat_events.created_on AS created_on, " + 
            "dat_events.update_user_id AS update_user_id, " + 
            "update_user.user_name AS update_user_name, " + 
            "update_user.email AS update_user_email, " +
            "dat_events.updated_on AS updated_on, " + 
            "dat_projectcomps.item_name AS item_name, " + 
            "dat_projectcomps.task_kbn AS task_kbn, " + 
            "dat_projectcomps.class_word1 AS class_word1, " + 
            "dat_projectcomps.class_word3 AS class_word2, " + 
            "dat_projectcomps.class_word3 AS class_word3 " + 
            "FROM dat_events JOIN dat_projectcomps ON dat_projectcomps.id=dat_events.project_tree_id " + 
            "LEFT JOIN mst_users AS create_user ON create_user.id=dat_events.create_user_id " + 
            "LEFT JOIN mst_users AS update_user ON update_user.id=dat_events.update_user_id " + 
            "WHERE dat_events.id = :id ;";


        public static const MAX_LINE_NO_QUERY:String =
            "SELECT MAX(line_no) AS max_line_no " +
            "FROM dat_projectcomps " + 
            "WHERE project_id = :project_id; ";

        public static const MAX_PROJECTCOMP_ID_QUERY:String =
            "SELECT MAX(id) AS max_projectcomp_id " +
            "FROM dat_projectcomps; ";

        public static const INSERT_PROJECTCOMP_DATA_QUERY:String =
            "INSERT INTO " + 
            "	dat_projectcomps " + 
            " ( " + 
            "	id, " + 
            "	project_id, " + 
            "	line_no, " + 
            "	task_kbn, " + 
            "	item_name, " + 
            "	class_word1, " + 
            "	class_word2, " + 
            "	class_word3, " + 
            "	create_user_id, " + 
            "	created_on, " + 
            "	update_user_id, " + 
            "	updated_on, " + 
            "	last_operation_kbn, " + 
            "	local_edit_flg " + 
            " ) " + 
            " VALUES " + 
            " ( " + 
            "	:id, " + 
            "	:project_id, " +
            "	:line_no, " + 
            "	:task_kbn, " + 
            "	:item_name, " + 
            "	:class_word1, " + 
            "	:class_word2, " + 
            "	:class_word3, " + 
            "	:create_user_id, " + 
            "	:created_on, " + 
            "	:update_user_id, " + 
            "	:updated_on, " + 
            "	:last_operation_kbn, " + 
            "	1 " + 
            " );";


        public static const INSERT_TASK_DATA_QUERY:String =
            "INSERT INTO " + 
            "	dat_tasks " + 
            " ( " + 
            "	project_tree_id, " + 
            "	priority_kbn, " + 
            "	plan_power, " + 
            "	fcas_power, " + 
            "	exp_power, " + 
            "	tani_kbn, " + 
            "	client_user_id, " + 
            "	main_user_id, " + 
            "	complete_date, " + 
            "	progress_kbn, " + 
            "	progress_rate, " + 
            "	memo, " + 
            "	task_cd, " + 
            "	start_date, " + 
            "	end_date, " + 
            "	result, " + 
            "	create_user_id, " + 
            "	created_on, " + 
            "	update_user_id, " + 
            "	updated_on, " + 
            "	last_operation_kbn, " + 
            "	local_edit_flg " + 
            " ) " + 
            " VALUES " + 
            " ( " + 
            "	:project_tree_id, " + 
            "	:priority_kbn, " + 
            "	:plan_power, " + 
            "	:fcas_power, " + 
            "	:exp_power, " + 
            "	:tani_kbn, " + 
            "	:client_user_id, " + 
            "	:main_user_id, " + 
            "	:complete_date, " + 
            "	:progress_kbn, " + 
            "	:progress_rate, " + 
            "	:memo, " + 
            "	:task_cd, " + 
            "	:start_date, " + 
            "	:end_date, " + 
            "	:result, " + 
            "	:create_user_id, " + 
            "	:created_on, " + 
            "	:update_user_id, " + 
            "	:updated_on, " + 
            "	:last_operation_kbn, " + 
            "	1 " + 
            " );";

        public static const INSERT_SYNC_TASK_DATA_QUERY:String =
            "INSERT INTO " + 
            "   dat_tasks " + 
            " ( " + 
            "   id, " + 
            "   project_tree_id, " + 
            "   priority_kbn, " + 
            "   plan_power, " + 
            "   fcas_power, " + 
            "   exp_power, " + 
            "   tani_kbn, " + 
            "   client_user_id, " + 
            "   main_user_id, " + 
            "   complete_date, " + 
            "   progress_kbn, " + 
            "   progress_rate, " + 
            "   memo, " + 
            "   task_cd, " + 
            "   start_date, " + 
            "   end_date, " + 
            "   result, " + 
            "   create_user_id, " + 
            "   created_on, " + 
            "   update_user_id, " + 
            "   updated_on, " + 
            "   last_operation_kbn, " + 
            "   local_edit_flg " + 
            " ) " + 
            " VALUES " + 
            " ( " + 
            "   :id, " + 
            "   :project_tree_id, " + 
            "   :priority_kbn, " + 
            "   :plan_power, " + 
            "   :fcas_power, " + 
            "   :exp_power, " + 
            "   :tani_kbn, " + 
            "   :client_user_id, " + 
            "   :main_user_id, " + 
            "   :complete_date, " + 
            "   :progress_kbn, " + 
            "   :progress_rate, " + 
            "   :memo, " + 
            "   :task_cd, " + 
            "   :start_date, " + 
            "   :end_date, " + 
            "   :result, " + 
            "   :create_user_id, " + 
            "   :created_on, " + 
            "   :update_user_id, " + 
            "   :updated_on, " + 
            "   :last_operation_kbn, " + 
            "   0 " + 
            " );";






/**** 初期化　****/
        public static const INSERT_USER_QUERY:String =
            "INSERT INTO mst_users " + 
            "(" + 
            "id," + 
            "login_id," + 
            "password," + 
            "user_name," + 
            "email," + 
            "name," + 
            "sex," + 
            "birthday," + 
            "company_name," + 
            "section_name," + 
            "zip," + 
            "prefecture," + 
            "address1," + 
            "address2," + 
            "tel," + 
            "fax," + 
            "start_date," + 
            "expire_date," + 
            "valid_flg," + 
            "create_user_id," + 
            "created_on," + 
            "update_user_id," + 
            "updated_on," + 
            //"srcpassword," + 
            "skype_id," + 
            "last_login_on" + 
            ") " + 
            "VALUES " + 
            "(" + 
            ":id," + 
            ":login_id," + 
            ":password," + 
            ":user_name," + 
            ":email," + 
            ":name," + 
            ":sex," + 
            ":birthday," + 
            ":company_name," + 
            ":section_name," + 
            ":zip," + 
            ":prefecture," + 
            ":address1," + 
            ":address2," + 
            ":tel," + 
            ":fax," + 
            ":start_date," + 
            ":expire_date," + 
            ":valid_flg," + 
            ":create_user_id," + 
            ":created_on," + 
            ":update_user_id," + 
            ":updated_on," + 
            //":srcpassword," + 
            ":skype_id," + 
            ":last_login_on" + 
            "); ";

        public static const INSERT_PROJECT_QUERY:String =
            "INSERT INTO dat_projects " + 
            "(id, project_cd, project_name, start_date, delivery_date, valid_flg, create_user_id, created_on, update_user_id, updated_on, end_user_name) " + 
            "VALUES " + 
            "(:id, :project_cd, :project_name, :start_date, :delivery_date, :valid_flg, :create_user_id, :created_on, :update_user_id, :updated_on, :end_user_name);";


        public static const INSERT_PROJECT_USERS_QUERY:String =
            "INSERT INTO dat_projectusers " + 
            "(id, project_id, email, user_id, create_user_id, created_on, active_flg) " + 
            "VALUES " + 
            "(:id, :project_id, :email, :user_id, :create_user_id, :created_on, :active_flg);";

        public static const INSERT_PROJECT_COMPS_QUERY:String =
            "INSERT INTO dat_projectcomps " + 
            "(id, project_id, line_no, task_kbn, item_name, class_word1, class_word2, class_word3, create_user_id, created_on, update_user_id, updated_on, last_operation_kbn) " + 
            "VALUES " + 
            "(:id, :project_id, :line_no, :task_kbn, :item_name, :class_word1, :class_word2, :class_word3, :create_user_id, :created_on, :update_user_id, :updated_on, :last_operation_kbn);";

        public static const INSERT_PROJECT_LOGS_QUERY:String =
            "INSERT INTO dat_projectlogs " + 
            "(id, projectcomp_id, log_kbn, create_user_id, created_on) " + 
            "VALUES " + 
            "(:id, :projectcomp_id, :log_kbn, :create_user_id, :created_on);";

        public static const INSERT_TASKS_QUERY:String =
            "INSERT INTO dat_tasks " + 
            "(id, project_tree_id, priority_kbn, plan_power, fcas_power, exp_power, tani_kbn, " + 
			"  client_user_id, " + 
			"  main_user_id, " + 
			"  complete_date, " + 
			"  progress_kbn, " + 
			"  progress_rate, " + 
			"  memo, " + 
			"  create_user_id, " + 
			"  created_on, " + 
			"  update_user_id, " + 
			"  updated_on, " + 
			"  task_cd, " + 
			"  start_date, " + 
			"  end_date, " + 
			"  report_date, " + 
			"  result, " + 
			"  last_operation_kbn) " + 
            "VALUES " + 
            "(:id, :project_tree_id, :priority_kbn, :plan_power, :fcas_power, :exp_power, :tani_kbn, " + 
			"  :client_user_id, " + 
			"  :main_user_id, " + 
			"  :complete_date, " + 
			"  :progress_kbn, " + 
			"  :progress_rate, " + 
			"  :memo, " + 
			"  :create_user_id, " + 
			"  :created_on, " + 
			"  :update_user_id, " + 
			"  :updated_on, " + 
			"  :task_cd, " + 
			"  :start_date, " + 
			"  :end_date, " + 
			"  :report_date, " + 
			"  :result, " + 
			"  :last_operation_kbn);";


        public static const INSERT_TASKUSERS_QUERY:String =
            "INSERT INTO dat_taskusers " + 
            "(id, " + 
            "task_id, " + 
			"  projectuser_id, " + 
			"  create_user_id, " + 
			"  created_on, " + 
            "  local_edit_flg " + 
			") " + 
            "VALUES " + 
            "(:id, " + 
            ":task_id, " + 
			":projectuser_id, " + 
			":create_user_id, " + 
			":created_on, " + 
            "0 " + 
			");";

        public static const INSERT_MILESTONES_QUERY:String =
            "INSERT INTO dat_milestones " + 
            "(id, " + 
            "project_tree_id, " + 
			"  mils_date, " + 
			"  create_user_id, " + 
			"  created_on, " + 
			"  update_user_id, " + 
			"  updated_on, " + 
			"  last_operation_kbn " + 
			") " + 
            "VALUES " + 
            "(:id, " + 
            ":project_tree_id, " + 
			":mils_date, " + 
			":create_user_id, " + 
			":created_on, " + 
			":update_user_id, " + 
			":updated_on, " + 
			":last_operation_kbn " + 
			");";


        public static const INSERT_EVENTS_QUERY:String =
            "INSERT INTO dat_events " + 
            "(id, " + 
            "project_tree_id, " + 
			"  start_date, " + 
			"  start_time, " + 
			"  end_date, " + 
			"  end_time, " + 
			"  allday_kbn, " + 
			"  place, " + 
			"  content, " + 
			"  create_user_id, " + 
			"  created_on, " + 
			"  update_user_id, " + 
			"  updated_on, " + 
			"  last_operation_kbn " + 
			") " + 
            "VALUES " + 
            "(:id, " + 
            ":project_tree_id, " + 
			":start_date, " + 
			":start_time, " + 
			":end_date, " + 
			":end_time, " + 
			":allday_kbn, " + 
			":place, " + 
			":content, " + 
			":create_user_id, " + 
			":created_on, " + 
			":update_user_id, " + 
			":updated_on, " + 
			":last_operation_kbn " + 
			");";

        public static const INSERT_EVENTUSERS_QUERY:String =
            "INSERT INTO dat_eventusers " + 
            "(id, " + 
            "event_id, " + 
			"  projectuser_id, " + 
			"  entry_flg, " + 
			"  create_user_id, " + 
			"  created_on " + 
			") " + 
            "VALUES " + 
            "(:id, " + 
            ":event_id, " + 
			":projectuser_id, " + 
			":entry_flg, " + 
			":create_user_id, " + 
			":created_on " + 
			");";


        public static const INSERT_MESSAGES_QUERY:String =
            "INSERT INTO mst_messages " + 
            "(id, " + 
            "msg_code, " + 
			"  msg_kbn, " + 
			"  msg_base, " + 
			"  msg_jp, " + 
			"  msg_cn, " + 
			"  msg_en, " + 
			"  valid_flg, " + 
			"  create_user_id, " + 
			"  created_on, " + 
			"  update_user_id, " + 
			"  updated_on " + 
			") " + 
            "VALUES " + 
            "(:id, " + 
            ":msg_code, " + 
			":msg_kbn, " + 
			":msg_base, " + 
			":msg_jp, " + 
			":msg_cn, " + 
			":msg_en, " + 
			":valid_flg, " + 
			":create_user_id, " + 
			":created_on, " + 
			":update_user_id, " + 
			":updated_on " + 
			");";


        //syncData
        public static const SYNC_PROJECTCOMPS_DATA:String =
            "SELECT * FROM dat_projectcomps WHERE local_edit_flg = 1 AND project_id = :project_id";

        public static const SYNC_TASKS_DATA:String =
            "SELECT dat_tasks.* FROM dat_tasks LEFT JOIN dat_projectcomps ON dat_projectcomps.id = dat_tasks.project_tree_id "
            + " WHERE dat_tasks.local_edit_flg = 1 AND dat_projectcomps.project_id = :project_id;";

        public static const SYNC_TASKUSERS_DATA:String =
            "SELECT dat_taskusers.* FROM dat_taskusers "
            + " LEFT JOIN dat_tasks ON dat_tasks.id = dat_taskusers.task_id "
            + " LEFT JOIN dat_projectcomps ON dat_projectcomps.id = dat_tasks.project_tree_id "
            + " WHERE dat_taskusers.local_edit_flg = 1 AND dat_projectcomps.project_id = :project_id;";

        public static const EXISTS_PROJECTCOMP_QUERY:String =
            "SELECT id FROM dat_projectcomps WHERE id = :id;"

        public static const EXISTS_TASK_QUERY:String =
            "SELECT id FROM dat_tasks WHERE id = :id;"

        public static const EXISTS_TASKUSERS_QUERY:String =
            "SELECT id FROM dat_taskusers WHERE id = :id;"


    }
}